﻿#if NETFRAMEWORK
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Reflection;
using NewLife;

namespace XCode.DataAccessLayer;

class Access : FileDbBase
{
    #region 属性
    /// <summary>返回数据库类型。外部DAL数据库类请使用Other</summary>
    public override DatabaseType Type => DatabaseType.Access;

    /// <summary>创建工厂</summary>
    /// <returns></returns>
    protected override DbProviderFactory CreateFactory() => OleDbFactory.Instance;
    #endregion

    #region 方法
    /// <summary>创建数据库会话</summary>
    /// <returns></returns>
    protected override IDbSession OnCreateSession() => new AccessSession(this);

    /// <summary>创建元数据对象</summary>
    /// <returns></returns>
    protected override IMetaData OnCreateMetaData() => new AccessMetaData();

    public override Boolean Support(String providerName)
    {
        providerName = providerName.ToLower();
        if (providerName.Contains("microsoft.jet.oledb")) return true;
        if (providerName.Contains("access")) return true;
        if (providerName.Contains("oledb")) return true;

        return false;
    }

    protected override void OnSetConnectionString(ConnectionStringBuilder builder)
    {
        base.OnSetConnectionString(builder);

        // 特别处理一下Excel
        var fn = DatabaseName;
        if (!fn.IsNullOrEmpty())
        {
            var ext = Path.GetExtension(fn);
            if (ext.EqualIgnoreCase(".xls")) builder.TryAdd("Extended Properties", "Excel 8.0");
        }
    }
    #endregion

    #region 数据库特性
    /// <summary>长文本长度</summary>
    public override Int32 LongTextLength => 255;

    public override String FormatName(String name)
    {
        if (!String.IsNullOrEmpty(name) && name.Contains("$"))
            return FormatKeyWord(name);
        else
            return base.FormatName(name);
    }

    /// <summary>格式化时间为SQL字符串</summary>
    /// <param name="column">字段</param>
    /// <param name="dateTime">时间值</param>
    /// <returns></returns>
    public override String FormatDateTime(IDataColumn column, DateTime dateTime) => $"#{dateTime:yyyy-MM-dd HH:mm:ss.fff}#";

    /// <summary>格式化关键字</summary>
    /// <param name="keyWord">关键字</param>
    /// <returns></returns>
    public override String FormatKeyWord(String keyWord)
    {
        if (String.IsNullOrEmpty(keyWord)) return keyWord;

        if (keyWord.StartsWith("[") && keyWord.EndsWith("]")) return keyWord;

        return $"[{keyWord}]";
    }

    /// <summary>格式化数据为SQL数据</summary>
    /// <param name="field">字段</param>
    /// <param name="value">数值</param>
    /// <returns></returns>
    public override String FormatValue(IDataColumn field, Object? value)
    {
        if (/*field != null &&*/ field.DataType == typeof(Boolean) || value != null && value.GetType() == typeof(Boolean))
        {
            if (value == null) return field.Nullable ? "null" : "";

            return value.ToString();
        }

        return base.FormatValue(field, value);
    }
    #endregion

    #region 分页
    public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
    {
        var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
        return new SelectBuilder(sql);
    }

    /// <summary>构造分页SQL</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="startRowIndex">开始行，0表示第一行</param>
    /// <param name="maximumRows">最大返回行数，0表示所有行</param>
    /// <param name="keyColumn">唯一键。用于not in分页</param>
    /// <returns>分页SQL</returns>
    public override String PageSplit(String sql, Int64 startRowIndex, Int64 maximumRows, String? keyColumn)
    {
        // 从第一行开始，不需要分页
        if (startRowIndex <= 0 && maximumRows < 1) return sql;

        return SqlServer.PageSplitByTopNotIn(sql, startRowIndex, maximumRows, keyColumn);
    }
    #endregion

    #region 平台检查
    /// <summary>是否支持</summary>
    public static void CheckSupport()
    {
        var module = typeof(Object).Module;
        module.GetPEKind(out var _, out var machine);

        if (machine != ImageFileMachine.I386) throw new NotSupportedException("64位平台不支持OLEDB驱动！");
    }
    #endregion
}

/// <summary>Access数据库</summary>
internal class AccessSession : FileDbSession
{
    #region 构造函数
    public AccessSession(IDatabase db) : base(db) => Access.CheckSupport();
    #endregion

    #region 方法
    #endregion

    #region 基本方法 查询/执行
    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public override Int64 InsertAndGetIdentity(String sql, CommandType type = CommandType.Text, params IDataParameter[]? ps)
    {
        BeginTransaction(IsolationLevel.Serializable);
        try
        {
            Int64 rs = Execute(sql, type, ps);
            if (rs > 0) rs = ExecuteScalar<Int64>("Select @@Identity");
            Commit();
            return rs;
        }
        catch { Rollback(true); throw; }
    }
    #endregion
}

/// <summary>Access元数据</summary>
class AccessMetaData : FileDbMetaData
{
    #region 构架
    protected override List<IDataTable> OnGetTables(String[]? names)
    {
        var dt = GetSchema(_.Tables, null);
        if (dt?.Rows == null || dt.Rows.Count <= 0) return new List<IDataTable>();

        // 默认列出所有字段
        var rows = dt.Select($"TABLE_TYPE='Table' Or TABLE_TYPE='View'");

        return GetTables(rows, names);
    }

    /// <summary>
    /// 快速取得所有表名
    /// </summary>
    /// <returns></returns>
    public override IList<String> GetTableNames()
    {
        var list = new List<String>();

        var dt = GetSchema(_.Tables, null);
        if (dt?.Rows == null || dt.Rows.Count <= 0) return list;

        // 默认列出所有字段
        var rows = dt.Select($"TABLE_TYPE='Table' Or TABLE_TYPE='View'");

        foreach (var dr in rows)
        {
            var tn = GetDataRowValue<String>(dr, _.TalbeName);
            if (!tn.IsNullOrEmpty()) list.Add(tn);
        }

        return list;
    }

    /// <summary>获取索引</summary>
    /// <param name="table"></param>
    /// <param name="indexes">索引</param>
    /// <param name="indexColumns">索引列</param>
    /// <returns></returns>
    protected override List<IDataIndex> GetIndexes(IDataTable table, DataTable? indexes, DataTable? indexColumns)
    {
        var list = base.GetIndexes(table, indexes, indexColumns);

        // Access的索引直接以索引字段的方式排布，所以需要重新组合起来
        var dic = new Dictionary<String, IDataIndex>();
        foreach (var item in list)
        {
            if (item.Name.IsNullOrEmpty()) continue;

            if (!dic.TryGetValue(item.Name, out var di))
            {
                dic.Add(item.Name, item);
            }
            else
            {
                var ss = new List<String>(di.Columns);
                if (item.Columns != null && item.Columns.Length > 0 && !ss.Contains(item.Columns[0]))
                {
                    ss.Add(item.Columns[0]);
                    di.Columns = ss.ToArray();
                }
            }
        }
        list.Clear();
        foreach (var item in dic.Values)
        {
            list.Add(item);
        }

        return list;
    }

    protected override String? GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
    {
        var str = base.GetFieldConstraints(field, onlyDefine);

        if (field.Identity) str = " AUTOINCREMENT(1,1)" + str;

        return str;
    }
    #endregion

    #region 反向工程创建表
    public override String CreateTableSQL(IDataTable table)
    {
        var sql = base.CreateTableSQL(table);

        var pks = table.PrimaryKeys;
        if (String.IsNullOrEmpty(sql) || pks.Length < 2) return sql;

        // 处理多主键
        var names = pks.Select(e => e.ColumnName).ToArray();
        var di = ModelHelper.GetIndex(table, names);
        if (di == null)
        {
            di = table.CreateIndex();
            di.PrimaryKey = true;
            di.Unique = true;
            di.Columns = names;
        }
        // Access里面的主键索引名必须叫这个
        di.Name = "PrimaryKey";

        sql += ";" + Environment.NewLine;
        sql += CreateIndexSQL(di);
        return sql;
    }

    public override String CreateIndexSQL(IDataIndex index)
    {
        var sql = base.CreateIndexSQL(index);
        if (String.IsNullOrEmpty(sql) || !index.PrimaryKey) return sql;

        return sql + " WITH PRIMARY";
    }
    #endregion

    #region 数据类型
    protected override String? GetFieldType(IDataColumn field)
    {
        var typeName = base.GetFieldType(field);

        //if (typeName.StartsWith("VarChar")) return typeName.Replace("VarChar", "Text");
        if (field.Identity) return null;

        return typeName;
    }

    /// <summary>数据类型映射</summary>
    private static readonly Dictionary<Type, String[]> _DataTypes = new()
    {
        { typeof(Byte[]), new String[] { "binary", "varbinary", "blob", "image", "general", "oleobject" } },
        { typeof(Guid), new String[] { "uniqueidentifier", "guid" } },
        { typeof(Boolean), new String[] { "bit", "yesno", "logical", "bool", "boolean" } },
        { typeof(Byte), new String[] { "tinyint" } },
        { typeof(Int16), new String[] { "smallint" } },
        { typeof(Int32), new String[] { "int" } },
        { typeof(Int64), new String[] { "integer", "counter", "autoincrement", "identity", "long", "bigint" } },
        { typeof(Single), new String[] { "single" } },
        { typeof(Double), new String[] { "real", "float", "double" } },
        { typeof(Decimal), new String[] { "money", "decimal", "currency", "numeric" } },
        { typeof(DateTime), new String[] { "datetime", "smalldate", "timestamp", "date", "time" } },
        { typeof(String), new String[] { "nvarchar({0})", "ntext", "varchar({0})", "memo({0})", "longtext({0})", "note({0})", "text({0})", "string({0})", "char({0})", "char({0})" } }
    };
    #endregion
}
#endif